package com.proactive.webapp.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import javax.sql.DataSource;

import com.mysql.jdbc.Statement;
import com.proactive.webapp.dao.ResourceDAO;
import com.proactive.webapp.entity.Resource;

public class ResourceDAOImpl implements ResourceDAO{
	private DataSource dataSource;
	private Connection conn = null ;
	private ResultSet rs = null ;
	private PreparedStatement smt = null ;
	
	public void setDataSource(DataSource dataSource) {
		this.dataSource = dataSource;
	}

	@Override
	public int insert(Resource resource) {
		// TODO Auto-generated method stub
		String sql = "INSERT INTO resource(resourceCategoryID, resourceTitle, "
				+ "resourceContent, resourceUrl, resourceStatus, resourceImage, "
				+ "memberAccount, createDate, updateDate) VALUES(?,?,?,?,?,?,?,NOW(),NOW())";
		int resourceID = 0; 
		try {
			conn = dataSource.getConnection();
			smt = conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
			smt.setInt(1, resource.getCategoryID());
			smt.setString(2, resource.getTitle());
			smt.setString(3, resource.getContent());
			smt.setString(4, resource.getUrl());
			smt.setInt(5, resource.getStatus());
			smt.setString(6, resource.getImagePath());
			smt.setString(7, resource.getAccount());
			smt.executeUpdate();
			rs = smt.getGeneratedKeys();
			if(rs.next()){
				resourceID = rs.getInt(1);
			}
			rs.close();
			smt.close();
 
		} catch (SQLException e) {
			throw new RuntimeException(e);
 
		} finally {
			if (conn != null) {
				try {
					conn.close();
				} catch (SQLException e) {}
			}
		}
		return resourceID;
	}

	@Override
	public void update(Resource resource) {
		// TODO Auto-generated method stub
		String sql = "UPDATE resource SET resourceCategoryID=?, resourceTitle=?, "
				+ "resourceContent=?, resourceUrl=?, resourceImage=?, updateDate=NOW() "
				+ "WHERE resourceID=?";
		try {
			conn = dataSource.getConnection();
			smt = conn.prepareStatement(sql);
			smt.setInt(1, resource.getCategoryID());
			smt.setString(2, resource.getTitle());
			smt.setString(3, resource.getContent());
			smt.setString(4, resource.getUrl());
			smt.setString(5, resource.getImagePath());
			smt.setInt(6, resource.getId());
			smt.executeUpdate();
			smt.close();
 
		} catch (SQLException e) {
			throw new RuntimeException(e);
 
		} finally {
			if (conn != null) {
				try {
					conn.close();
				} catch (SQLException e) {}
			}
		}
	}

	@Override
	public void updateStatus(Resource resource) {
		// TODO Auto-generated method stub
		String sql = "UPDATE resource SET resourceStatus = ? WHERE resourceID = ?";
		try {
			conn = dataSource.getConnection();
			smt = conn.prepareStatement(sql);
			smt.setInt(1, resource.getStatus());
			smt.setInt(2, resource.getId());
			smt.executeUpdate();
			smt.close();
 
		} catch (SQLException e) {
			throw new RuntimeException(e);
 
		} finally {
			if (conn != null) {
				try {
					conn.close();
				} catch (SQLException e) {}
			}
		}
	}

	@Override
	public void updateViewCount(Resource resource) {
		// TODO Auto-generated method stub
		String sql = "UPDATE resource SET resourceViewCount = ? WHERE resourceID = ?";
		try {
			conn = dataSource.getConnection();
			smt = conn.prepareStatement(sql);
			smt.setInt(1, resource.getViewCount());
			smt.setInt(2, resource.getId());
			smt.executeUpdate();
			smt.close();
 
		} catch (SQLException e) {
			throw new RuntimeException(e);
 
		} finally {
			if (conn != null) {
				try {
					conn.close();
				} catch (SQLException e) {}
			}
		}
	}

	@Override
	public Resource get(Resource resource) {
		// TODO Auto-generated method stub
		String sql = "SELECT * FROM resource a JOIN resource_category b "
				+ "ON a.resourceCategoryID = b.resourceCategoryID WHERE resourceID = ?";
		try {
			conn = dataSource.getConnection();
			smt = conn.prepareStatement(sql);
			smt.setInt(1, resource.getId());
			rs = smt.executeQuery();
			if(rs.next()){
				resource.setCategoryName(rs.getString("resourceCategoryName"));
				resource.setCategoryID(rs.getInt("resourceCategoryID"));
				resource.setTitle(rs.getString("resourceTitle"));
				resource.setContent(rs.getString("resourceContent"));
				resource.setUrl(rs.getString("resourceUrl"));
				resource.setImagePath(rs.getString("resourceImage"));
				resource.setCategoryID(rs.getInt("resourceCategoryID"));
				resource.setAccount(rs.getString("memberAccount"));
				resource.setViewCount(rs.getInt("resourceViewCount"));
			}
			rs.close();
			smt.close();
 
		} catch (SQLException e) {
			throw new RuntimeException(e);
 
		} finally {
			if (conn != null) {
				try {
					conn.close();
				} catch (SQLException e) {}
			}
		}
		return resource;
	}

	@Override
	public List<Resource> getListByType(String loginAccount,String type, int start, int size) {
		// TODO Auto-generated method stub
		List<Resource> resourceList = new ArrayList<Resource>();
		String sql = "";
		switch(type){
			case "new":
				sql = "SELECT *,(SELECT COUNT(*) FROM resource_report b WHERE a.resourceID = b.resourceID) "
						+ "AS reportCount,(SELECT COUNT(*) FROM resource_collect c WHERE a.resourceID = c.resourceID AND c.memberAccount=?) "
						+ "AS collect,(SELECT COUNT(*) FROM resource_report d WHERE a.resourceID = d.resourceID AND d.memberAccount=?) "
						+ "AS report FROM resource a WHERE resourceStatus = 1 HAVING reportCount < 10 "
						+ "ORDER BY createDate DESC LIMIT ?,?";
				break;
			case "hot":
				sql = "SELECT *,(SELECT COUNT(*) FROM resource_report b WHERE a.resourceID = b.resourceID) "
						+ "AS reportCount,(SELECT COUNT(*) FROM resource_collect c WHERE a.resourceID = c.resourceID AND c.memberAccount=?) "
						+ "AS collect,(SELECT COUNT(*) FROM resource_report d WHERE a.resourceID = d.resourceID AND d.memberAccount=?) "
						+ "AS report FROM resource a WHERE resourceStatus = 1 HAVING reportCount < 10 "
						+ "ORDER BY resourceViewCount DESC, createDate DESC LIMIT ?,?";
				break;
			case "col":
				sql = "SELECT *,(SELECT COUNT(*) FROM resource_collect b WHERE a.resourceID = b.resourceID) "
						+ "AS collectCount,(SELECT COUNT(*) FROM resource_report c WHERE a.resourceID = c.resourceID) "
						+ "AS reportCount,(SELECT COUNT(*) FROM resource_collect d WHERE a.resourceID = d.resourceID AND d.memberAccount=?) "
						+ "AS collect,(SELECT COUNT(*) FROM resource_report e WHERE a.resourceID = e.resourceID AND e.memberAccount=?) "
						+ "AS report FROM resource a WHERE resourceStatus = 1 HAVING reportCount < 10 "
						+ "ORDER BY collectCount DESC, createDate DESC LIMIT ?,?";
				break;
			default:
				sql = "SELECT *,(SELECT COUNT(*) FROM resource_report b WHERE a.resourceID = b.resourceID) "
						+ "AS reportCount,(SELECT COUNT(*) FROM resource_collect c WHERE a.resourceID = c.resourceID AND c.memberAccount=?) "
						+ "AS collect,(SELECT COUNT(*) FROM resource_report d WHERE a.resourceID = d.resourceID AND d.memberAccount=?) "
						+ "AS report FROM resource a WHERE resourceStatus = 1 HAVING reportCount < 10 "
						+ "ORDER BY createDate DESC LIMIT ?,?";
				break;
		}
		
		try {
			conn = dataSource.getConnection();
			smt = conn.prepareStatement(sql);
			smt.setString(1, loginAccount);
			smt.setString(2, loginAccount);
			smt.setInt(3, start);
			smt.setInt(4, size);
			rs = smt.executeQuery();
			while(rs.next()){
				Resource resource = new Resource();
				resource.setId(rs.getInt("resourceID"));
				resource.setTitle(rs.getString("resourceTitle"));
				resource.setContent(rs.getString("resourceContent"));
				resource.setUrl(rs.getString("resourceUrl"));
				resource.setImagePath(rs.getString("resourceImage"));
				resource.setViewCount(rs.getInt("resourceViewCount"));
				resource.setReportFlag(rs.getInt("report")==0?false:true);
				resource.setCollectFlag(rs.getInt("collect")==0?false:true);
				resource.setCollectString(resource.isCollectFlag()?"已收藏":"收藏");
				resource.setCollectString1(resource.isCollectFlag()?"none":"inline");
				resource.setCollectString2(resource.isCollectFlag()?"inline":"none");
				resource.setCollectClass(resource.isCollectFlag()?"topfocusCollect":"");
				resourceList.add(resource);
			}
			rs.close();
			smt.close();
 
		} catch (SQLException e) {
			throw new RuntimeException(e);
 
		} finally {
			if (conn != null) {
				try {
					conn.close();
				} catch (SQLException e) {}
			}
		}
		
		return resourceList;
	}

	@Override
	public List<Resource> getListByCategory(String loginAccount, String type, int resourceCategoryID,
			int start, int size) {
		// TODO Auto-generated method stub
		List<Resource> resourceList = new ArrayList<Resource>();
		String sql = "";
		switch(type){
			case "new":
				sql = "SELECT *,(SELECT COUNT(*) FROM resource_report b WHERE a.resourceID = b.resourceID) "
						+ "AS reportCount,(SELECT COUNT(*) FROM resource_collect c WHERE a.resourceID = c.resourceID AND c.memberAccount=?) "
						+ "AS collect,(SELECT COUNT(*) FROM resource_report d WHERE a.resourceID = d.resourceID AND d.memberAccount=?) "
						+ "AS report FROM resource a WHERE resourceCategoryID = ? AND resourceStatus = 1 HAVING reportCount < 10 "
						+ "ORDER BY createDate DESC LIMIT ?,?";
				break;
			case "hot":
				sql = "SELECT *,(SELECT COUNT(*) FROM resource_report b WHERE a.resourceID = b.resourceID) "
						+ "AS reportCount,(SELECT COUNT(*) FROM resource_collect c WHERE a.resourceID = c.resourceID AND c.memberAccount=?) "
						+ "AS collect,(SELECT COUNT(*) FROM resource_report d WHERE a.resourceID = d.resourceID AND d.memberAccount=?) "
						+ "AS report FROM resource a WHERE resourceCategoryID = ? AND resourceStatus = 1 HAVING reportCount < 10 "
						+ "ORDER BY resourceViewCount DESC, createDate DESC LIMIT ?,?";
				break;
			case "col":
				sql = "SELECT *,(SELECT COUNT(*) FROM resource_collect b WHERE a.resourceID = b.resourceID) "
						+ "AS collectCount,(SELECT COUNT(*) FROM resource_report c WHERE a.resourceID = c.resourceID) "
						+ "AS reportCount,(SELECT COUNT(*) FROM resource_collect d WHERE a.resourceID = d.resourceID AND d.memberAccount=?) "
						+ "AS collect,(SELECT COUNT(*) FROM resource_report e WHERE a.resourceID = e.resourceID AND e.memberAccount=?) "
						+ "AS report FROM resource a WHERE resourceCategoryID = ? AND resourceStatus = 1 HAVING reportCount < 10 "
						+ "ORDER BY collectCount DESC, createDate DESC LIMIT ?,?";
				break;
			default:
				sql = "SELECT *,(SELECT COUNT(*) FROM resource_report b WHERE a.resourceID = b.resourceID) "
						+ "AS reportCount,(SELECT COUNT(*) FROM resource_collect c WHERE a.resourceID = c.resourceID AND c.memberAccount=?) "
						+ "AS collect,(SELECT COUNT(*) FROM resource_report d WHERE a.resourceID = d.resourceID AND d.memberAccount=?) "
						+ "AS report FROM resource a WHERE resourceCategoryID = ? AND resourceStatus = 1 HAVING reportCount < 10 "
						+ "ORDER BY createDate DESC LIMIT ?,?";
				break;
		}
		
		try {
			conn = dataSource.getConnection();
			smt = conn.prepareStatement(sql);
			smt.setString(1, loginAccount);
			smt.setString(2, loginAccount);
			smt.setInt(3, resourceCategoryID);
			smt.setInt(4, start);
			smt.setInt(5, size);
			rs = smt.executeQuery();
			while(rs.next()){
				Resource resource = new Resource();
				resource.setId(rs.getInt("resourceID"));
				resource.setTitle(rs.getString("resourceTitle"));
				resource.setContent(rs.getString("resourceContent"));
				resource.setUrl(rs.getString("resourceUrl"));
				resource.setImagePath(rs.getString("resourceImage"));
				resource.setViewCount(rs.getInt("resourceViewCount"));
				resource.setReportFlag(rs.getInt("report")==0?false:true);
				resource.setCollectFlag(rs.getInt("collect")==0?false:true);
				resource.setCollectString(resource.isCollectFlag()?"已收藏":"收藏");
				resource.setCollectString1(resource.isCollectFlag()?"none":"inline");
				resource.setCollectString2(resource.isCollectFlag()?"inline":"none");
				resource.setCollectClass(resource.isCollectFlag()?"topfocusCollect":"");
				resourceList.add(resource);
			}
			rs.close();
			smt.close();
 
		} catch (SQLException e) {
			throw new RuntimeException(e);
 
		} finally {
			if (conn != null) {
				try {
					conn.close();
				} catch (SQLException e) {}
			}
		}
		
		return resourceList;
	}

	@Override
	public int countTotal(int resourceCategoryID) {
		// TODO Auto-generated method stub
		int count = 0;
		try {			
			conn = dataSource.getConnection();
			if(resourceCategoryID != 0){
				String sql = "SELECT *,(SELECT COUNT(*) FROM resource_report b WHERE a.resourceID = b.resourceID HAVING COUNT(*)<10) "
						+ "AS reportCount FROM resource a WHERE resourceCategoryID = ? AND resourceStatus = 1 "
						+ "HAVING reportCount IS NOT NULL";	
				smt = conn.prepareStatement(sql);
				smt.setInt(1, resourceCategoryID);
			}
			else{
				String sql = "SELECT *,(SELECT COUNT(*) FROM resource_report b WHERE a.resourceID = b.resourceID HAVING COUNT(*)<10) "
						+ "AS reportCount FROM resource a WHERE resourceStatus = 1 HAVING reportCount IS NOT NULL";	
				smt = conn.prepareStatement(sql);
			}
			
			rs = smt.executeQuery();
			rs.beforeFirst();  
			rs.last(); 
			count = rs.getRow();
			
			rs.close();
			smt.close();
			
			
		} catch (SQLException e) {
			throw new RuntimeException(e);
 
		} finally {
			if (conn != null) {
				try {
					conn.close();
				} catch (SQLException e) {}
			}
		}
		return count;
	}

	

	@Override
	public List<Resource> getListByWriter(String loginAccount, int start, int size) {
		// TODO Auto-generated method stub
		List<Resource> resourceList = new ArrayList<Resource>();
		String sql = "SELECT *,(SELECT COUNT(*) FROM resource_report b WHERE a.resourceID = b.resourceID) "
				+ "AS reportCount,(SELECT COUNT(*) FROM resource_collect c WHERE a.resourceID = c.resourceID AND c.memberAccount=?) "
				+ "AS collect,(SELECT COUNT(*) FROM resource_report d WHERE a.resourceID = d.resourceID AND d.memberAccount=?) "
				+ "AS report FROM resource a WHERE resourceStatus != 3 AND a.memberAccount = ? "
				+ "HAVING reportCount < 10 ORDER BY createDate DESC LIMIT ?,?";
		
		try {
			conn = dataSource.getConnection();
			smt = conn.prepareStatement(sql);
			smt.setString(1, loginAccount);
			smt.setString(2, loginAccount);
			smt.setString(3, loginAccount);
			smt.setInt(4, start);
			smt.setInt(5, size);
			rs = smt.executeQuery();
			while(rs.next()){
				Resource resource = new Resource();
				resource.setId(rs.getInt("resourceID"));
				resource.setTitle(rs.getString("resourceTitle"));
				resource.setContent(rs.getString("resourceContent"));
				resource.setUrl(rs.getString("resourceUrl"));
				resource.setImagePath(rs.getString("resourceImage"));
				resource.setViewCount(rs.getInt("resourceViewCount"));
				resource.setStatus(rs.getInt("resourceStatus"));
				resource.setActString(resource.getStatus()==2?"顯示":"隱藏");
				resource.setActClass(resource.getStatus()==2?"show":"hide");
				resource.setHideClass(resource.getStatus()==2?"edithide":"");
				resource.setReportFlag(rs.getInt("report")==0?false:true);
				resource.setCollectFlag(rs.getInt("collect")==0?false:true);
				resource.setCollectString(resource.isCollectFlag()?"已收藏":"收藏");
				resource.setCollectString1(resource.isCollectFlag()?"none":"inline");
				resource.setCollectString2(resource.isCollectFlag()?"inline":"none");
				resource.setCollectClass(resource.isCollectFlag()?"topfocusCollect":"");
				resourceList.add(resource);
			}
			rs.close();
			smt.close();
 
		} catch (SQLException e) {
			throw new RuntimeException(e);
 
		} finally {
			if (conn != null) {
				try {
					conn.close();
				} catch (SQLException e) {}
			}
		}
		
		return resourceList;
	}


	@Override
	public List<Resource> getListByMember(String account, String loginAccount,
			int start, int size) {
		// TODO Auto-generated method stub
		List<Resource> resourceList = new ArrayList<Resource>();
		String sql = "SELECT *,(SELECT COUNT(*) FROM resource_report b WHERE a.resourceID = b.resourceID) "
				+ "AS reportCount,(SELECT COUNT(*) FROM resource_collect c WHERE a.resourceID = c.resourceID AND c.memberAccount=?) "
				+ "AS collect,(SELECT COUNT(*) FROM resource_report d WHERE a.resourceID = d.resourceID AND d.memberAccount=?) "
				+ "AS report FROM resource a WHERE resourceStatus = 1 AND a.memberAccount = ? "
				+ "HAVING reportCount < 10 ORDER BY createDate DESC LIMIT ?,?";
		
		try {
			conn = dataSource.getConnection();
			smt = conn.prepareStatement(sql);
			smt.setString(1, loginAccount);
			smt.setString(2, loginAccount);
			smt.setString(3, account);
			smt.setInt(4, start);
			smt.setInt(5, size);
			rs = smt.executeQuery();
			while(rs.next()){
				Resource resource = new Resource();
				resource.setId(rs.getInt("resourceID"));
				resource.setTitle(rs.getString("resourceTitle"));
				resource.setContent(rs.getString("resourceContent"));
				resource.setUrl(rs.getString("resourceUrl"));
				resource.setImagePath(rs.getString("resourceImage"));
				resource.setViewCount(rs.getInt("resourceViewCount"));
				resource.setStatus(rs.getInt("resourceStatus"));
				resource.setActString(resource.getStatus()==2?"顯示":"隱藏");
				resource.setActClass(resource.getStatus()==2?"show":"hide");
				resource.setHideClass(resource.getStatus()==2?"edithide":"");
				resource.setReportFlag(rs.getInt("report")==0?false:true);
				resource.setCollectFlag(rs.getInt("collect")==0?false:true);
				resource.setCollectString(resource.isCollectFlag()?"已收藏":"收藏");
				resource.setCollectString1(resource.isCollectFlag()?"none":"inline");
				resource.setCollectString2(resource.isCollectFlag()?"inline":"none");
				resource.setCollectClass(resource.isCollectFlag()?"topfocusCollect":"");
				resourceList.add(resource);
			}
			rs.close();
			smt.close();
 
		} catch (SQLException e) {
			throw new RuntimeException(e);
 
		} finally {
			if (conn != null) {
				try {
					conn.close();
				} catch (SQLException e) {}
			}
		}
		
		return resourceList;
	}
	

	@Override
	public int countTotalByWriter(String account) {
		// TODO Auto-generated method stub
		int count = 0;
		try {			
			conn = dataSource.getConnection();
			String sql = "SELECT *,(SELECT COUNT(*) FROM resource_report b WHERE a.resourceID = b.resourceID HAVING COUNT(*)<10) "
					+ "AS reportCount FROM resource a WHERE memberAccount = ? AND resourceStatus != 3 HAVING reportCount IS NOT NULL";	
			smt = conn.prepareStatement(sql);
			smt.setString(1, account);
			rs = smt.executeQuery();
			rs.beforeFirst();  
			rs.last(); 
			count =rs.getRow();
			
			rs.close();
			smt.close();
			
			
		} catch (SQLException e) {
			throw new RuntimeException(e);
 
		} finally {
			if (conn != null) {
				try {
					conn.close();
				} catch (SQLException e) {}
			}
		}
		return count;
	}

	@Override
	public int countTotalByMember(String account) {
		// TODO Auto-generated method stub
		int count = 0;
		try {			
			conn = dataSource.getConnection();
			String sql = "SELECT *,(SELECT COUNT(*) FROM resource_report b WHERE a.resourceID = b.resourceID HAVING COUNT(*)<10) "
					+ "AS reportCount FROM resource a WHERE memberAccount = ? AND resourceStatus = 1 HAVING reportCount IS NOT NULL";	
			smt = conn.prepareStatement(sql);
			smt.setString(1, account);
			rs = smt.executeQuery();
			rs.beforeFirst();  
			rs.last(); 
			count =rs.getRow();
			
			rs.close();
			smt.close();
			
			
		} catch (SQLException e) {
			throw new RuntimeException(e);
 
		} finally {
			if (conn != null) {
				try {
					conn.close();
				} catch (SQLException e) {}
			}
		}
		return count;
	}
}
